package cn.com.headfree.mouldshow.dao.impl;

import cn.com.headfree.entity.*;
import cn.com.headfree.mouldshow.dao.HumanDataDao;
import cn.com.headfree.vo.simple.*;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("/humanDataDao")
public class HumanDataDaoImpl extends BaseDaoImpl<TEmpBaseInfo> implements HumanDataDao {
    /**
     * 基本信息临时数据
     * @param
     */
    @Override
    public List<EmpBaseInfoVo> getTEmpBaseInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.name as user_name,t.erp_no,t.sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,t.nation,t.birth_place,t.politi_status,t.id_card_no,t.mobile_phone,t.`level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t  where 1=1 and t.erp_no = ? order by create_time desc ";
//        sql = "select t.id,u.xb_name as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
//        sql = "select t.id,u.`NAME` as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
//        sql = "select t.id,u.`NAME` as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,t.party_date,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
        sql = "select t.id,u.`NAME` as user_name,t.erp_no,t.sex,t.birth_date,t.nation,t.birth_place,t.born_place,t.politi_status,t.id_card_no,t.mobile_phone,t.`level`,t.salary_grade,t.party_date,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
//        sqlbuilder.append(" from t_emp_base_info ");
//        sqlbuilder.append(" where 1=1 ");
//        sqlbuilder.append("and erp_no = ?");
//        sqlbuilder.append(" order by create_time desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(EmpBaseInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<EmpBaseInfoVo> getTEmpBaseInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.name as user_name,t.erp_no,t.sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,t.nation,t.birth_place,t.politi_status,t.id_card_no,t.mobile_phone,t.`level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t  where 1=1 and t.erp_no = ? order by create_time desc ";
//        sql = "select t.id,u.xb_name as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
//        sql = "select t.id,u.`NAME` as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,DATE_FORMAT(t.birth_date,'%Y-%m-%d %H:%i:%s') as birth_date_str,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,DATE_FORMAT(t.party_date,'%Y-%m-%d %H:%i:%s')as party_date_str,t.party_date,DATE_FORMAT(t.job_date,'%Y-%m-%d %H:%i:%s') as job_date_str,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
//        sql = "select t.id,u.`NAME` as user_name,t.erp_no,GET_DIC('Gender',t.sex) as sex,t.birth_date,GET_DIC('Nation',t.nation) as nation,t.birth_place,t.born_place,GET_DIC('PoliticalLandscapeText',t.politi_status) as politi_status,t.id_card_no,t.mobile_phone,GET_DIC('StaffLevel',t.`level`) as `level`,t.salary_grade,t.party_date,t.job_date,t.unit,t.dept,t.cur_post from t_emp_base_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no = ? AND t.is_valid='0' AND t.del_sign='0' order by create_time desc ";
        sql = "SELECT t.id,u.`NAME` as user_name,t.erp_no,t.sex,t.birth_date,t.nation,t.birth_place,t.born_place,t.politi_status,t.id_card_no,t.mobile_phone,t.`level`,t.salary_grade,t.party_date,t.job_date,t.unit,t.dept,t.cur_post";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM isc_user u ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" LEFT JOIN t_dept_emp_rela TDER ON u.fax = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN t_emp_base_info t ON t.erp_no = u.fax ");
        sqlbuilder.append(" where 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and u.FAX=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' order by create_time desc");
//        sqlbuilder.append("and erp_no = ?");
//        sqlbuilder.append(" order by create_time desc  ");

        return findAllBySQL(EmpBaseInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //学历信息临时
    @Override
    public List<EducateInfoVo> getTEducateInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,t.degree_name,t.grad_school,t.school_type,t.major,t.acqu_time,u.xb_name as user_name from t_educate_info t LEFT JOIN t_isc_user_tmp u on t.erp_no=u.SAPHRID where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,GET_DIC('PersonnelQualifications',t.educ_name) as educ_name,GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.grad_school,GET_DIC('CollegeCategory',t.school_type) as school_type,t.major,t.acqu_time,u.xb_name as user_name,t.is_cont_educ,DATE_FORMAT(t.start_time,'%Y-%m-%d %H:%i:%s') as start_time_str,DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s') as end_time_str,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str from t_educate_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.grad_school,GET_DIC('CollegeCategory',t.school_type) as school_type,t.major,t.acqu_time,u.`NAME` as user_name,t.is_cont_educ from t_educate_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,t.degree_name,t.grad_school,t.school_type,t.major,t.acqu_time,u.`NAME` as user_name,t.is_cont_educ from t_educate_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.erp_no,t.start_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(EducateInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<EducateInfoVo> getTEducateInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,t.degree_name,t.grad_school,t.school_type,t.major,t.acqu_time,u.xb_name as user_name from t_educate_info t LEFT JOIN t_isc_user_tmp u on t.erp_no=u.SAPHRID where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,GET_DIC('PersonnelQualifications',t.educ_name) as educ_name,GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.grad_school,GET_DIC('CollegeCategory',t.school_type) as school_type,t.major,t.acqu_time,u.xb_name as user_name,t.is_cont_educ,DATE_FORMAT(t.start_time,'%Y-%m-%d %H:%i:%s') as start_time_str,DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s') as end_time_str,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str from t_educate_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.grad_school,GET_DIC('CollegeCategory',t.school_type) as school_type,t.major,t.acqu_time,u.`NAME` as user_name,t.is_cont_educ from t_educate_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.educ_name,t.degree_name,t.grad_school,t.school_type,t.major,t.acqu_time,u.`NAME` as user_name,t.is_cont_educ ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_educate_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append("INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u on t.erp_no=u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.start_time DESC ");
        return findAllBySQL(EducateInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //工作经历临时
    @Override
    public List<WorkExpInfoVo> getTWorkExpInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_date,DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,t.end_date,DATE_FORMAT(t.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,t.user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time desc";
//        sql = "select t.id,t.erp_no,t.start_date,DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,t.end_date,DATE_FORMAT(t.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,u.xb_name as user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_date desc";
//        sql = "select t.id,t.erp_no,t.start_date,t.end_date,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,u.`NAME` as user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_date desc";
        sql = "select t.id,t.erp_no,t.start_date,t.end_date,t.company,t.dept,t.post,t.rank,u.`NAME` as user_name,t.job_content,t.post_type from t_work_exp_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_date desc";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(WorkExpInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<WorkExpInfoVo> getTWorkExpInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_date,DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,t.end_date,DATE_FORMAT(t.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,t.user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time desc";
//        sql = "select t.id,t.erp_no,t.start_date,DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,t.end_date,DATE_FORMAT(t.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,u.xb_name as user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_date desc";
//        sql = "select t.id,t.erp_no,t.start_date,t.end_date,t.company,t.dept,t.post,GET_DIC('DutyLevel',t.rank) as rank,u.`NAME` as user_name,t.job_content,GET_DIC('PostCategory',t.post_type) as post_type from t_work_exp_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_date desc";
        sql = "select t.id,t.erp_no,t.start_date,t.end_date,t.company,t.dept,t.post,t.rank,u.`NAME` as user_name,t.job_content,t.post_type ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append("from t_work_exp_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.start_date DESC");
        return findAllBySQL(WorkExpInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //教育培训及授课经历临时
    @Override
    public List<ContiStudyInfoVo> getTContiStudyInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,t.type,u.xb_name as user_name from t_conti_study_info t LEFT JOIN t_isc_user_tmp u on t.erp_no=u.SAPHRID where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,DATE_FORMAT(t.start_time,'%Y-%m-%d %H:%i:%s') as start_time_str,t.end_time,DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s') as end_time_str,t.major,t.study_place,GET_DIC('PeixunshoukeType',t.type) as type,t.certifier,u.xb_name as user_name from t_conti_study_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,GET_DIC('PeixunshoukeType',t.type) as type,t.certifier,u.`NAME` as user_name from t_conti_study_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc";
        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,t.type,t.certifier,u.`NAME` as user_name from t_conti_study_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(ContiStudyInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<ContiStudyInfoVo> getTContiStudyInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,t.type,u.xb_name as user_name from t_conti_study_info t LEFT JOIN t_isc_user_tmp u on t.erp_no=u.SAPHRID where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,DATE_FORMAT(t.start_time,'%Y-%m-%d %H:%i:%s') as start_time_str,t.end_time,DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s') as end_time_str,t.major,t.study_place,GET_DIC('PeixunshoukeType',t.type) as type,t.certifier,u.xb_name as user_name from t_conti_study_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc ";
//        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,GET_DIC('PeixunshoukeType',t.type) as type,t.certifier,u.`NAME` as user_name from t_conti_study_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.start_time desc";
        sql = "select t.id,t.erp_no,t.start_time,t.end_time,t.major,t.study_place,t.type,t.certifier,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_conti_study_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.start_time DESC");
        return findAllBySQL(ContiStudyInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //个人荣誉信息临时
    @Override
    public List<HonorInfoVo> getTHonorInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,GET_DIC('JygexinQcJb',t.honor_level) as honor_level,GET_DIC('RuYuLeiBie',t.honor_type) as honor_type,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_honor_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,GET_DIC('JygexinQcJb',t.honor_level) as honor_level,GET_DIC('RuYuLeiBie',t.honor_type) as honor_type,t.acqu_time,u.`NAME` as user_name from t_honor_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,t.honor_level,t.honor_type,t.acqu_time,u.`NAME` as user_name from t_honor_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(HonorInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<HonorInfoVo> getTHonorInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,GET_DIC('JygexinQcJb',t.honor_level) as honor_level,GET_DIC('RuYuLeiBie',t.honor_type) as honor_type,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_honor_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,GET_DIC('JygexinQcJb',t.honor_level) as honor_level,GET_DIC('RuYuLeiBie',t.honor_type) as honor_type,t.acqu_time,u.`NAME` as user_name from t_honor_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.honor_title,t.award_org,t.honor_level,t.honor_type,t.acqu_time,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_honor_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.acqu_time DESC");
        return findAllBySQL(HonorInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //竞赛考试信息临时
    @Override
    public List<ContestInfoVo> getTContestInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank,t.get_title,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.xb_name as user_name from t_contest_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.award_date desc ";
//        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank,t.get_title,t.award_date,u.`NAME` as user_name from t_contest_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.award_date desc ";
        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,t.award_level,t.comp_rank,t.get_title,t.award_date,u.`NAME` as user_name from t_contest_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.award_date desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(ContestInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<ContestInfoVo> getTContestInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank,t.get_title,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.xb_name as user_name from t_contest_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.award_date desc ";
//        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank,t.get_title,t.award_date,u.`NAME` as user_name from t_contest_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.award_date desc ";
        sql = "select t.id,t.erp_no,t.comp_name,t.host_org,t.award_level,t.comp_rank,t.get_title,t.award_date,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_contest_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.award_date DESC");
        return findAllBySQL(ContestInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //专家人才信息临时
    @Override
    public List<ExpertTitleInfoVo> getTExpertTitleInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,DATE_FORMAT(t.emp_term_start,'%Y-%m-%d %H:%i:%s') as emp_term_start_str,t.emp_term_end,DATE_FORMAT(t.emp_term_end,'%Y-%m-%d %H:%i:%s') as emp_term_end_str,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.major,t.level_name,t.type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,t.emp_title from t_expert_title_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(ExpertTitleInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<ExpertTitleInfoVo> getTExpertTitleInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,DATE_FORMAT(t.emp_term_start,'%Y-%m-%d %H:%i:%s') as emp_term_start_str,t.emp_term_end,DATE_FORMAT(t.emp_term_end,'%Y-%m-%d %H:%i:%s') as emp_term_end_str,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.major,t.level_name,t.type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,t.emp_title ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_expert_title_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.acqu_time DESC");
        return findAllBySQL(ExpertTitleInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //专业技术资格信息临时
    @Override
    public List<ProTecInfoVo> getTProTecInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.subj_name,GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set,GET_DIC('ProfessionalQualifications',t.subj_level) as subj_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_pro_tec_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,t.subj_name,GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set,GET_DIC('ProfessionalQualifications',t.subj_level) as subj_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_pro_tec_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.subj_name,t.subj_set,t.subj_level,t.audit_org,t.org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_pro_tec_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(ProTecInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<ProTecInfoVo> getTProTecInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.subj_name,GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set,GET_DIC('ProfessionalQualifications',t.subj_level) as subj_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_pro_tec_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,t.subj_name,GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set,GET_DIC('ProfessionalQualifications',t.subj_level) as subj_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_pro_tec_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.subj_name,t.subj_set,t.subj_level,t.audit_org,t.org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_pro_tec_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.acqu_time DESC");
        return findAllBySQL(ProTecInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //技能等级信息临时
    @Override
    public List<SkillLevelInfoVo> getTSkillLevelInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,GET_DIC('SkillAppraisalWork',t.tech_type) as tech_type,GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_skill_level_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,GET_DIC('SkillAppraisalWork',t.tech_type) as tech_type,GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_skill_level_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.tech_type,t.qualifi_level,t.audit_org,t.org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_skill_level_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(SkillLevelInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<SkillLevelInfoVo> getTSkillLevelInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,GET_DIC('SkillAppraisalWork',t.tech_type) as tech_type,GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name from t_skill_level_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
//        sql = "select t.id,t.erp_no,GET_DIC('SkillAppraisalWork',t.tech_type) as tech_type,GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level,t.audit_org,GET_DIC('IdentificationUnitLevel',t.org_level) as org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name from t_skill_level_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time desc ";
        sql = "select t.id,t.erp_no,t.tech_type,t.qualifi_level,t.audit_org,t.org_level,t.cert_no,t.acqu_time,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_skill_level_info t ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.acqu_time DESC");
        return findAllBySQL(SkillLevelInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //导师带徒信息临时
    //须要重新修改
    /**
     * @param erpNo
     * @return
     */
    @Override
    public List<SdtTutorPrentInfoVo> getTSdtTutorPrentInfoPageList(String erpNo) {
        Object[] params=new Object[1];
        String sql = "";
        sql = "select t0.id,t0.start_date,t0.end_date,t0.erp_no,t0.tutor_name,tdpi.erp_no AS prent_erp_no,up.`NAME` AS user_name,t0.teach_summary,tdpi.win_info AS prent_award_name,tdpi.id AS award_id from (select * from t_sdt_tutor_prent_info tstpi where tstpi.erp_no=?) t0 LEFT JOIN isc_user ut on t0.erp_no=ut.FAX INNER JOIN t_sdt_prent_info tdpi on tdpi.sdt_id=t0.id LEFT JOIN isc_user up on tdpi.erp_no=up.FAX where 1=1 and t0.is_valid='0' and t0.del_sign='0' ORDER BY tdpi.user_name,t0.start_date DESC";
        params[0]=erpNo;
        return findAllBySQL(SdtTutorPrentInfoVo.class,sql,null,false,params);
//        sql = "select tstpi.id,tstpi.start_date,DATE_FORMAT(tstpi.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,tstpi.end_date,DATE_FORMAT(tstpi.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,tstpi.erp_no,ut.`NAME` AS tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` AS user_name,tstpi.teach_summary,tdpi.win_info as prent_award_name,tdpi.id as award_id ";
//        StringBuilder sqlbuilder = new StringBuilder(sql);
//        sqlbuilder.append(" from t_sdt_tutor_prent_info tstpi ");
////        sqlbuilder.append(" INNER JOIN t_isc_user_tmp ut on tstpi.erp_no=ut.hr_erp ");
//        sqlbuilder.append(" INNER JOIN isc_user ut ON tstpi.erp_no = ut.FAX ");
//        sqlbuilder.append(" INNER JOIN t_sdt_prent_info tdpi on tstpi.id=tdpi.sdt_id and tstpi.erp_no=?");
//        sqlbuilder.append(" LEFT JOIN isc_user up ON tdpi.erp_no = up.FAX ");
////        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on tdpi.erp_no=u.SAPHRID ");
//        sqlbuilder.append(" where 1=1 ");
//        sqlbuilder.append(" AND tstpi.is_valid='0' AND tstpi.del_sign='0' ");
//        sqlbuilder.append(" order by tdpi.user_name,tstpi.start_date desc  ");
//        params[0]=erpNo;
//        return findAllBySQL(SdtTutorPrentInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<SdtTutorPrentInfoVo> getTSdtTutorPrentInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        if (StringUtils.isNotBlank(erpNo)){
//            sql = "select tstpi.id,tstpi.start_date,tstpi.end_date,tstpi.erp_no,ut.`NAME` as tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` as user_name,tstpi.teach_summary,tdpi.win_info as prent_award_name,tdpi.id as award_id ";
//        }else if (StringUtils.isNotBlank(deptId)){
//            sql = "select tstpi.id,tstpi.start_date,tstpi.end_date,tstpi.erp_no,ut.`NAME` as tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` as user_name,tstpi.teach_summary,GROUP_CONCAT(tdpi.win_info) as prent_award_name,tdpi.id as award_id ";
//        }
        sql = "select tstpi.id,tstpi.start_date,tstpi.end_date,tstpi.erp_no,ut.`NAME` as tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` as user_name,tstpi.teach_summary,tdpi.id as award_id,GROUP_CONCAT(tsa.award) as prent_award_name ";
        StringBuilder stringBuilder=new StringBuilder(sql);
        stringBuilder.append(" from t_sdt_tutor_prent_info tstpi ");
        if (StringUtils.isNotBlank(deptId)){
            stringBuilder.append(" INNER JOIN t_dept_emp_rela d ON tstpi.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        stringBuilder.append(" LEFT JOIN isc_user ut on tstpi.erp_no=ut.FAX ");
        stringBuilder.append(" LEFT JOIN t_sdt_prent_info tdpi on tstpi.id=tdpi.sdt_id ");
        stringBuilder.append(" LEFT JOIN isc_user up on tdpi.erp_no=up.FAX ");
        stringBuilder.append(" LEFT JOIN t_sdt_award tsa on tsa.sdt_td_id = tdpi.id ");
        stringBuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            stringBuilder.append(" and tstpi.erp_no=? ");
            stringBuilder.append(" AND tstpi.is_valid='0' AND tstpi.del_sign='0' GROUP BY tdpi.id ORDER BY tstpi.start_date");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            stringBuilder.append(" and TOD.path like concat('%',?,'%') ");
            stringBuilder.append(" AND tstpi.is_valid='0' AND tstpi.del_sign='0' GROUP BY tdpi.id ORDER BY tstpi.id,tstpi.start_date");
//            stringBuilder.append(" AND tstpi.is_valid='0' AND tstpi.del_sign='0' GROUP BY tdpi.erp_no ORDER BY tstpi.start_date");
        }
        return findAllBySQL(SdtTutorPrentInfoVo.class,stringBuilder.toString(),null,false,params);
//        sql = "select tstpi.id,tstpi.start_date,DATE_FORMAT(tstpi.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,tstpi.end_date,DATE_FORMAT(tstpi.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,tstpi.erp_no,ut.`NAME` AS tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` AS user_name,tstpi.teach_summary,tdpi.win_info as prent_award_name,tdpi.id as award_id ";
//        StringBuilder sqlbuilder = new StringBuilder(sql);
//        sqlbuilder.append(" from t_sdt_tutor_prent_info tstpi ");
////        sqlbuilder.append(" INNER JOIN t_isc_user_tmp ut on tstpi.erp_no=ut.hr_erp ");
//        sqlbuilder.append(" INNER JOIN isc_user ut ON tstpi.erp_no = ut.FAX ");
//        sqlbuilder.append(" INNER JOIN t_sdt_prent_info tdpi on tstpi.id=tdpi.sdt_id and tstpi.erp_no=?");
//        sqlbuilder.append(" LEFT JOIN isc_user up ON tdpi.erp_no = up.FAX ");
////        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on tdpi.erp_no=u.SAPHRID ");
//        sqlbuilder.append(" where 1=1 ");
//        sqlbuilder.append(" AND tstpi.is_valid='0' AND tstpi.del_sign='0' ");
//        sqlbuilder.append(" order by tdpi.user_name,tstpi.start_date desc  ");
//        params[0]=erpNo;
//        return findAllBySQL(SdtTutorPrentInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //年度绩效临时
    @Override
    public List<YearEffectInfoVo> getTYearEffectInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.erp_no,t.rate_year_start,DATE_FORMAT(t.rate_year_start,'%Y-%m-%d %H:%i:%s') as rate_year_start_str,t.rate_year_end,DATE_FORMAT(t.rate_year_end,'%Y-%m-%d %H:%i:%s') as rate_year_end_str,t.rate_year,t.perfor_grade,t.resi_inte,t.cur_inte,t.user_name from t_year_effect_info t where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.rate_year_start,DATE_FORMAT(t.rate_year_start,'%Y-%m-%d %H:%i:%s') as rate_year_start_str,t.rate_year_end,DATE_FORMAT(t.rate_year_end,'%Y-%m-%d %H:%i:%s') as rate_year_end_str,t.rate_year,GET_DIC('PerformLevelRecentThreeYears',t.perfor_grade) as perfor_grade,t.resi_inte,t.cur_inte,u.xb_name as user_name from t_year_effect_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.rate_year desc ";
        sql = "select t.id,t.erp_no,t.rate_year_start,t.rate_year_end,t.rate_year,t.perfor_grade,t.resi_inte,t.cur_inte,u.`NAME` as user_name from t_year_effect_info t LEFT JOIN isc_user u on t.erp_no=u.FAX where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.rate_year desc ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(YearEffectInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<YearEffectInfoVo> getTYearEffectInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.erp_no,t.rate_year_start,DATE_FORMAT(t.rate_year_start,'%Y-%m-%d %H:%i:%s') as rate_year_start_str,t.rate_year_end,DATE_FORMAT(t.rate_year_end,'%Y-%m-%d %H:%i:%s') as rate_year_end_str,t.rate_year,t.perfor_grade,t.resi_inte,t.cur_inte,t.user_name from t_year_effect_info t where 1=1 and t.erp_no=? ORDER BY t.create_time desc ";
//        sql = "select t.id,t.erp_no,t.rate_year_start,DATE_FORMAT(t.rate_year_start,'%Y-%m-%d %H:%i:%s') as rate_year_start_str,t.rate_year_end,DATE_FORMAT(t.rate_year_end,'%Y-%m-%d %H:%i:%s') as rate_year_end_str,t.rate_year,GET_DIC('PerformLevelRecentThreeYears',t.perfor_grade) as perfor_grade,t.resi_inte,t.cur_inte,u.xb_name as user_name from t_year_effect_info t INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp where 1=1 and t.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.rate_year desc ";
        sql = "select t.id,t.erp_no,t.rate_year_start,t.rate_year_end,t.rate_year,t.perfor_grade,t.resi_inte,t.cur_inte,u.`NAME` as user_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append("FROM t_year_effect_info t");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON t.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON t.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and t.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.erp_no,t.rate_year DESC");
        return findAllBySQL(YearEffectInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //学术成果临时
    @Override
    public List<AcadeAchiveInfoVo> getTAcadeAchiveInfoPageList(String erpNo) {
        String sql = "";
//        sql = "SELECT t.id,t.acad_name,GET_DIC('XueshuchengguoType',t.acad_type) as acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,GET_DIC('XueshuchengguoType',t.LEVEL) as LEVEL,t.pub_time,DATE_FORMAT(t.pub_time,'%Y-%m-%d %H:%i:%s') as pub_time_str,u.xb_name as user_name,GET_DIC('XueshuchengguoJs',tpr.role) as role,tpr.erp_no,tpr.rank FROM t_part_rank tpr INNER JOIN t_isc_user_tmp u on tpr.erp_no=u.hr_erp INNER JOIN t_acade_achive_info t ON tpr.item_id=t.id WHERE tpr.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.pub_time DESC";
//        sql = "SELECT t.id,t.acad_name,GET_DIC('XueshuchengguoType',t.acad_type) as acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,GET_DIC('XueshuchengguoJb',t.LEVEL) as LEVEL,t.pub_time,u.`NAME` as user_name,GET_DIC('XueshuchengguoJs',tpr.role) as role,tpr.erp_no,tpr.rank FROM t_part_rank tpr LEFT JOIN isc_user u on tpr.erp_no=u.FAX INNER JOIN t_acade_achive_info t ON tpr.item_id=t.id WHERE tpr.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.pub_time DESC";
        sql = "SELECT t.id,t.acad_name,t.acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,t.LEVEL,t.pub_time,u.`NAME` as user_name,tpr.role as role,tpr.erp_no,tpr.rank FROM t_part_rank tpr LEFT JOIN isc_user u on tpr.erp_no=u.FAX INNER JOIN t_acade_achive_info t ON tpr.item_id=t.id WHERE tpr.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.pub_time DESC";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(AcadeAchiveInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<AcadeAchiveInfoVo> getTAcadeAchiveInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "SELECT t.id,t.acad_name,GET_DIC('XueshuchengguoType',t.acad_type) as acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,GET_DIC('XueshuchengguoType',t.LEVEL) as LEVEL,t.pub_time,DATE_FORMAT(t.pub_time,'%Y-%m-%d %H:%i:%s') as pub_time_str,u.xb_name as user_name,GET_DIC('XueshuchengguoJs',tpr.role) as role,tpr.erp_no,tpr.rank FROM t_part_rank tpr INNER JOIN t_isc_user_tmp u on tpr.erp_no=u.hr_erp INNER JOIN t_acade_achive_info t ON tpr.item_id=t.id WHERE tpr.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.pub_time DESC";
//        sql = "SELECT t.id,t.acad_name,GET_DIC('XueshuchengguoType',t.acad_type) as acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,GET_DIC('XueshuchengguoJb',t.LEVEL) as LEVEL,t.pub_time,u.`NAME` as user_name,GET_DIC('XueshuchengguoJs',tpr.role) as role,tpr.erp_no,tpr.rank FROM t_part_rank tpr LEFT JOIN isc_user u on tpr.erp_no=u.FAX INNER JOIN t_acade_achive_info t ON tpr.item_id=t.id WHERE tpr.erp_no=? AND t.is_valid='0' AND t.del_sign='0' ORDER BY t.pub_time DESC";
        sql = "SELECT t.id,t.acad_name,t.acad_type,t.summary,t.page,t.pub_name,t.issn,t.periodic_no,t.publisher,t.LEVEL,t.pub_time,u.`NAME` as user_name,p.role,p.erp_no,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" FROM t_acade_achive_info t ");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON t.id = p.item_id ");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='12' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.id,t.pub_time DESC");
        return findAllBySQL(AcadeAchiveInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //标准制定信息临时
    @Override
    public List<StandInfoVo> getTStandInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.issue_time,DATE_FORMAT(t.issue_time,'%Y-%m-%d %H:%i:%s') as issue_time_str,t.`name`,t.type,t.`level`,p.role,p.erp_no,p.user_name,p.rank ";
//        sql = "select t.id,t.issue_time,DATE_FORMAT(t.issue_time,'%Y-%m-%d %H:%i:%s') as issue_time_str,t.`name`,GET_DIC('ZdbzType',t.type) as type,GET_DIC('StandardLevel',t.`level`) as `level`,p.role,p.erp_no,u.xb_name as user_name,p.rank ";
//        sql = "select t.id,t.issue_time,t.`name`,GET_DIC('ZdbzType',t.type) as type,GET_DIC('StandardLevel',t.`level`) as `level`,GET_DIC('XueshuchengguoJs',p.role) as role,p.erp_no,u.`NAME` AS user_name,p.rank ";
        sql = "select t.id,t.issue_time,t.`name`,t.type,t.`level`,p.role,p.erp_no,u.`NAME` AS user_name,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_stand_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no=u.hr_erp");
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no = u.SAPHRID");
        sqlbuilder.append(" where 1=1 ");
        sqlbuilder.append(" and p.item_type='13' ");
        sqlbuilder.append(" and p.erp_no = ? ");
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' ");
        sqlbuilder.append(" order by t.id,t.issue_time desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(StandInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<StandInfoVo> getTStandInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.issue_time,DATE_FORMAT(t.issue_time,'%Y-%m-%d %H:%i:%s') as issue_time_str,t.`name`,t.type,t.`level`,p.role,p.erp_no,p.user_name,p.rank ";
//        sql = "select t.id,t.issue_time,DATE_FORMAT(t.issue_time,'%Y-%m-%d %H:%i:%s') as issue_time_str,t.`name`,GET_DIC('ZdbzType',t.type) as type,GET_DIC('StandardLevel',t.`level`) as `level`,p.role,p.erp_no,u.xb_name as user_name,p.rank ";
//        sql = "select t.id,t.issue_time,t.`name`,GET_DIC('ZdbzType',t.type) as type,GET_DIC('StandardLevel',t.`level`) as `level`,GET_DIC('XueshuchengguoJs',p.role) as role,p.erp_no,u.`NAME` AS user_name,p.rank ";
        sql = "select t.id,t.issue_time,t.`name`,t.type,t.`level`,p.role,p.erp_no,u.`NAME` AS user_name,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_stand_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='13' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.id,t.issue_time DESC");
        return findAllBySQL(StandInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //专利信息临时
    @Override
    public List<PatentInfoVo> getTPatentInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.patent_name,t.patent_no,GET_DIC('AcademicDegree',t.patent_type) as patent_type,t.apply_date,t.decl_date,u.`NAME` AS user_name,p.erp_no,p.rank ";
        sql = "select t.id,t.patent_name,t.patent_no,t.patent_type,t.apply_date,t.decl_date,u.`NAME` AS user_name,p.erp_no,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_patent_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no=u.hr_erp");
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no = u.SAPHRID");
        sqlbuilder.append(" where 1=1 ");
        sqlbuilder.append(" and p.erp_no = ? ");
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' ");
        sqlbuilder.append(" order by t.apply_date desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(PatentInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<PatentInfoVo> getTPatentInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.patent_name,t.patent_no,GET_DIC('AcademicDegree',t.patent_type) as patent_type,t.apply_date,t.decl_date,u.`NAME` AS user_name,p.erp_no,p.rank ";
        sql = "select t.id,t.patent_name,t.patent_no,t.patent_type,t.apply_date,t.decl_date,u.`NAME` AS user_name,p.erp_no,p.rank ";
//        sql = "select a.id,a.patent_name,a.patent_no,a.patent_type,a.apply_date,a.decl_date,a.user_name,a.erp_no,a.rank from t_part_rank tt inner join ( ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
//        sqlbuilder.append(" select t.id,t.patent_name,t.patent_no,t.patent_type,t.apply_date,t.decl_date,u.`NAME` AS user_name,p.erp_no,p.rank ");
        sqlbuilder.append(" from t_patent_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='14' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ");
        sqlbuilder.append("  ORDER BY t.id,p.rank,t.apply_date DESC");
        return findAllBySQL(PatentInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //技艺革新信息临时
    @Override
    public List<TecInnovInfoVo> getTTecInnovInfoPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.xb_name as user_name,p.erp_no,t.create_time,DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%i:%s') as create_time_str ";
//        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,u.`NAME` AS user_name,p.erp_no,t.create_time ";
        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,t.award_level,t.award_grade,t.group_name,t.award_date,u.`NAME` AS user_name,p.erp_no,t.create_time,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_tec_innov_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no=u.hr_erp");
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX");
        sqlbuilder.append(" where 1=1 ");
        sqlbuilder.append(" and p.item_type='15' ");
        sqlbuilder.append(" and p.erp_no = ? ");
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' ");
        sqlbuilder.append(" order by t.create_time desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(TecInnovInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<TecInnovInfoVo> getTTecInnovInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.xb_name as user_name,p.erp_no,t.create_time,DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%i:%s') as create_time_str ";
//        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,u.`NAME` AS user_name,p.erp_no,t.create_time ";
        sql = "select t.id,t.achi_name,t.award_name,t.sumary,t.award_org,t.award_level,t.award_grade,t.group_name,t.award_date,u.`NAME` AS user_name,p.erp_no,t.create_time,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_tec_innov_info t ");
//        sqlbuilder.append(" LEFT JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='15' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.id,p.rank,t.create_time DESC");
        return findAllBySQL(TecInnovInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    //科技创新成果信息临时
    //成果名称,成果类别,内容摘要,记录形成时间,获奖时间,奖项授予单位,获奖级别,奖项等级,小组名称,获奖成员ERP编号,获奖成员姓名
    @Override
    public List<TecManInnovVo> getTTecManInnovPageList(String erpNo) {
        String sql = "";
//        sql = "select t.id,t.achi_name,GET_DIC('AchiveType',t.achi_type) as achi_type,t.award_name,t.summary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no ";
        sql = "select t.id,t.achi_name,t.achi_type,t.award_name,t.summary,t.award_org,t.award_level,t.award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no,p.rank,t.award_name ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_tec_man_innov t ");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" LEFT JOIN isc_user u on p.erp_no=u.FAX");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no = u.SAPHRID");
        sqlbuilder.append(" where 1=1 ");
        sqlbuilder.append(" and p.erp_no = ? ");
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' ");
        sqlbuilder.append(" order by t.create_time desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(TecManInnovVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<TecManInnovVo> getTTecManInnovPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
//        sql = "select t.id,t.achi_name,GET_DIC('AchiveType',t.achi_type) as achi_type,t.award_name,t.summary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no ";
        sql = "select t.id,t.achi_name,t.achi_type,t.award_name,t.summary,t.award_org,t.award_level,t.award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no,p.rank ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_tec_man_innov t ");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='16' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.id,t.create_time DESC");
        return findAllBySQL(TecManInnovVo.class,sqlbuilder.toString(),null,false,params);
    }

    //项目课题成果信息临时
    @Override
    public List<ProjAchieveInfoVo> getTProjAchieveInfoPageList(String erpNo) {
        String sql = "";
        sql = "select t.id,t.start_date,t.end_date,t.proj_name,t.summary,t.proj_level,p.role,p.erp_no,u.`NAME` as user_name,p.rank,t.encourage ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_proj_achieve_info t ");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        sqlbuilder.append(" LEFT JOIN isc_user u on p.erp_no=u.FAX");
//        sqlbuilder.append(" LEFT JOIN t_isc_user_tmp u on p.erp_no = u.SAPHRID");
        sqlbuilder.append(" where 1=1 ");
        sqlbuilder.append(" and p.erp_no = ? ");
        sqlbuilder.append(" AND t.is_valid='0' AND t.del_sign='0' ");
        sqlbuilder.append(" order by t.start_date desc  ");
        Object[] params=new Object[1];
        params[0]=erpNo;
        return findAllBySQL(ProjAchieveInfoVo.class,sqlbuilder.toString(),null,false,params);
    }

    @Override
    public List<ProjAchieveInfoVo> getTProjAchieveInfoPageList(String erpNo,String deptId) {
        Object[] params=new Object[1];
        String sql = "";
        sql = "select t.id,t.start_date,t.end_date,t.proj_name,t.summary,t.proj_level,p.role,p.erp_no,u.`NAME` as user_name,p.rank,t.encourage ";
        StringBuilder sqlbuilder = new StringBuilder(sql);
        sqlbuilder.append(" from t_proj_achieve_info t ");
        sqlbuilder.append(" INNER JOIN t_part_rank p ON p.item_id = t.id");
        if (StringUtils.isNotBlank(deptId)){
            sqlbuilder.append(" INNER JOIN t_dept_emp_rela d ON p.erp_no = d.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = d.dept_id ");
        }
        sqlbuilder.append(" LEFT JOIN isc_user u ON p.erp_no = u.FAX ");
        sqlbuilder.append(" WHERE 1=1 ");
        sqlbuilder.append(" and p.item_type='17' ");
        if (StringUtils.isNotBlank(erpNo)){
            params[0]=erpNo;
            sqlbuilder.append(" and p.erp_no=? ");
        }else if (StringUtils.isNotBlank(deptId)){
            params[0]=deptId;
            sqlbuilder.append(" and TOD.path like concat('%',?,'%') ");
        }
        sqlbuilder.append(" AND t.is_valid = '0' AND t.del_sign = '0' ORDER BY t.id,t.start_date DESC");
        return findAllBySQL(ProjAchieveInfoVo.class,sqlbuilder.toString(),null,false,params);
    }


    //参与人员
    @Override
    public void deleteTPartRankByCondition(String orgId, String itemId) {
        String sql = "delete from t_part_rank ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" where 1=1 ");
        Map<String, Object> params = new HashMap<String, Object>();
        if(StringUtils.isNotBlank(orgId)){
            sqlBuilder.append(" and org_id=#{orgId,jdbcType=VARCHAR}");
            params.put("orgId",orgId);
        }
        if(StringUtils.isNotBlank(itemId)){
            sqlBuilder.append(" and item_id=#{itemId,jdbcType=VARCHAR}");
            params.put("itemId", itemId);
        }
        this.executeSql(sqlBuilder.toString(), params);
    }

    //导师带徒删除徒弟信息
    @Override
    public void deleteTSdtPrentInfoByCondition(String orgId, String sdtId) {
        String sql = "delete from t_sdt_prent_info ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" where 1=1 ");
        Map<String, Object> params = new HashMap<String, Object>();
        if(StringUtils.isNotBlank(orgId)){
            sqlBuilder.append(" and org_id=#{orgId,jdbcType=VARCHAR}");
            params.put("orgId",orgId);
        }
        if(StringUtils.isNotBlank(sdtId)){
            sqlBuilder.append(" and sdt_id=#{sdtId,jdbcType=VARCHAR}");
            params.put("sdtId", sdtId);
        }
        this.executeSql(sqlBuilder.toString(), params);
    }

    //导师带徒根据徒弟ID删除徒弟所获奖项
    @Override
    public void deleteTSdtAwardBySdtTdId(String sdtId) {
        String sql = "delete from t_sdt_award ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" where 1=1 ");
        Map<String, Object> params = new HashMap<String, Object>();
        sqlBuilder.append(" and sdt_td_id=#{sdtTdId,jdbcType=VARCHAR}");
        params.put("sdtTdId", sdtId);
        this.executeSql(sqlBuilder.toString(), params);
    }
}
